package com.scau.RBAC.dao.impl;

import com.scau.RBAC.dao.PermissionDao;
import com.scau.RBAC.model.entity.Permission;
import com.scau.RBAC.util.DBConnectionSingleton;
import com.scau.RBAC.util.DatabaseException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class MySqlPermissionDao implements PermissionDao {

    @Override
    public int insert(Permission entity) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement(
                    "INSERT INTO permissions(permission_name, permission_code, description, parent_id, resource_path, sort_order, status, created_time, updated_time, created_by, updated_by) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

            ps.setString(1, entity.getPermissionName());
            ps.setString(2, entity.getPermissionCode());
            ps.setString(3, entity.getDescription());
            ps.setLong(4, entity.getParentId() != null ? entity.getParentId() : 0);
            ps.setString(5, entity.getResourcePath());
            ps.setInt(6, entity.getSortOrder() != null ? entity.getSortOrder() : 0);
            ps.setInt(7, entity.getStatus() != null ? entity.getStatus() : 0);
            ps.setObject(8, entity.getCreatedTime());
            ps.setObject(9, entity.getUpdatedTime());
            ps.setLong(10, entity.getCreatedBy() != null ? entity.getCreatedBy() : 0);
            ps.setLong(11, entity.getUpdatedBy() != null ? entity.getUpdatedBy() : 0);

            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new DatabaseException("插入权限失败", e);
        } finally {
            closeResources(ps, null);
        }
    }

    @Override
    public int batchInsert(List<Permission> entities) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            conn.setAutoCommit(false);

            ps = conn.prepareStatement(
                    "INSERT INTO permissions(permission_name, permission_code, description, parent_id, resource_path, sort_order, status, created_time, updated_time, created_by, updated_by) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

            for (Permission permission : entities) {
                ps.setString(1, permission.getPermissionName());
                ps.setString(2, permission.getPermissionCode());
                ps.setString(3, permission.getDescription());
                ps.setLong(4, permission.getParentId() != null ? permission.getParentId() : 0);
                ps.setString(5, permission.getResourcePath());
                ps.setInt(6, permission.getSortOrder() != null ? permission.getSortOrder() : 0);
                ps.setInt(7, permission.getStatus() != null ? permission.getStatus() : 0);
                ps.setObject(8, permission.getCreatedTime());
                ps.setObject(9, permission.getUpdatedTime());
                ps.setLong(10, permission.getCreatedBy() != null ? permission.getCreatedBy() : 0);
                ps.setLong(11, permission.getUpdatedBy() != null ? permission.getUpdatedBy() : 0);
                ps.addBatch();
            }

            int[] results = ps.executeBatch();
            conn.commit();

            int total = 0;
            for (int result : results) {
                total += result;
            }
            return total;
        } catch (SQLException e) {
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            throw new DatabaseException("批量插入权限失败", e);
        } finally {
            try {
                if (conn != null) {
                    conn.setAutoCommit(true);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            closeResources(ps, null);
        }
    }

    @Override
    public int deleteById(Long id) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement("DELETE FROM permissions WHERE id = ?");
            ps.setLong(1, id);
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new DatabaseException("删除权限失败", e);
        } finally {
            closeResources(ps, null);
        }
    }

    @Override
    public int batchDelete(List<Long> ids) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            conn.setAutoCommit(false);

            ps = conn.prepareStatement("DELETE FROM permissions WHERE id = ?");

            for (Long id : ids) {
                ps.setLong(1, id);
                ps.addBatch();
            }

            int[] results = ps.executeBatch();
            conn.commit();

            int total = 0;
            for (int result : results) {
                total += result;
            }
            return total;
        } catch (SQLException e) {
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            throw new DatabaseException("批量删除权限失败", e);
        } finally {
            try {
                if (conn != null) {
                    conn.setAutoCommit(true);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            closeResources(ps, null);
        }
    }

    @Override
    public int update(Permission entity) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement(
                    "UPDATE permissions SET permission_name = ?, permission_code = ?, description = ?, parent_id = ?, resource_path = ?, sort_order = ?, status = ?, updated_time = ?, updated_by = ? WHERE id = ?");

            ps.setString(1, entity.getPermissionName());
            ps.setString(2, entity.getPermissionCode());
            ps.setString(3, entity.getDescription());
            ps.setLong(4, entity.getParentId() != null ? entity.getParentId() : 0);
            ps.setString(5, entity.getResourcePath());
            ps.setInt(6, entity.getSortOrder() != null ? entity.getSortOrder() : 0);
            ps.setInt(7, entity.getStatus() != null ? entity.getStatus() : 0);
            ps.setObject(8, entity.getUpdatedTime());
            ps.setLong(9, entity.getUpdatedBy() != null ? entity.getUpdatedBy() : 0);
            ps.setLong(10, entity.getId());

            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new DatabaseException("更新权限失败", e);
        } finally {
            closeResources(ps, null);
        }
    }

    @Override
    public Permission findById(Long id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement("SELECT * FROM permissions WHERE id = ?");
            ps.setLong(1, id);
            rs = ps.executeQuery();

            if (rs.next()) {
                return mapResultSetToPermission(rs);
            }
            return null;
        } catch (SQLException e) {
            throw new DatabaseException("根据ID查询权限失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    @Override
    public List<Permission> findAll() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement("SELECT * FROM permissions");
            rs = ps.executeQuery();

            List<Permission> permissions = new ArrayList<>();
            while (rs.next()) {
                permissions.add(mapResultSetToPermission(rs));
            }
            return permissions;
        } catch (SQLException e) {
            throw new DatabaseException("查询所有权限失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    @Override
    public List<Permission> findByPage(Map<String, Object> params, int pageNum, int pageSize) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            StringBuilder sql = new StringBuilder("SELECT * FROM permissions WHERE 1=1");
            List<Object> paramList = new ArrayList<>();

            // 构建查询条件
            buildQueryConditions(sql, params, paramList);

            // 添加分页
            sql.append(" LIMIT ? OFFSET ?");
            paramList.add(pageSize);
            paramList.add((pageNum - 1) * pageSize);

            ps = conn.prepareStatement(sql.toString());

            // 设置参数
            for (int i = 0; i < paramList.size(); i++) {
                ps.setObject(i + 1, paramList.get(i));
            }

            rs = ps.executeQuery();
            List<Permission> permissions = new ArrayList<>();
            while (rs.next()) {
                permissions.add(mapResultSetToPermission(rs));
            }
            return permissions;
        } catch (SQLException e) {
            throw new DatabaseException("分页查询权限失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    @Override
    public long count(Map<String, Object> params) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM permissions WHERE 1=1");
            List<Object> paramList = new ArrayList<>();

            // 构建查询条件
            buildQueryConditions(sql, params, paramList);

            ps = conn.prepareStatement(sql.toString());

            // 设置参数
            for (int i = 0; i < paramList.size(); i++) {
                ps.setObject(i + 1, paramList.get(i));
            }

            rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getLong(1);
            }
            return 0;
        } catch (SQLException e) {
            throw new DatabaseException("统计权限数量失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    @Override
    public List<Permission> findByRoleId(Long roleId) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement(
                    "SELECT p.* FROM permissions p INNER JOIN role_permissions rp ON p.id = rp.permission_id WHERE rp.role_id = ?");
            ps.setLong(1, roleId);
            rs = ps.executeQuery();

            List<Permission> permissions = new ArrayList<>();
            while (rs.next()) {
                permissions.add(mapResultSetToPermission(rs));
            }
            return permissions;
        } catch (SQLException e) {
            throw new DatabaseException("根据角色ID查询权限失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    @Override
    public List<Permission> findByUserId(Long userId) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement(
                    "SELECT DISTINCT p.* FROM permissions p "
                            + "INNER JOIN role_permissions rp ON p.id = rp.permission_id "
                            + "INNER JOIN user_roles ur ON rp.role_id = ur.role_id "
                            + "WHERE ur.user_id = ?");
            ps.setLong(1, userId);
            rs = ps.executeQuery();

            List<Permission> permissions = new ArrayList<>();
            while (rs.next()) {
                permissions.add(mapResultSetToPermission(rs));
            }
            return permissions;
        } catch (SQLException e) {
            throw new DatabaseException("根据用户ID查询权限失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    @Override
    public List<Permission> findPermissionTree(Long parentId) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();

            String sql;
            if (parentId == null || parentId == 0) {
                // 查询顶级权限
                sql = "SELECT * FROM permissions WHERE parent_id IS NULL OR parent_id = 0 ORDER BY sort_order ASC";
                ps = conn.prepareStatement(sql);
            } else {
                // 查询子权限
                sql = "SELECT * FROM permissions WHERE parent_id = ? ORDER BY sort_order ASC";
                ps = conn.prepareStatement(sql);
                ps.setLong(1, parentId);
            }

            rs = ps.executeQuery();
            List<Permission> permissions = new ArrayList<>();
            while (rs.next()) {
                permissions.add(mapResultSetToPermission(rs));
            }

            // 递归查询子权限
            for (Permission permission : permissions) {
                List<Permission> children = findPermissionTree(permission.getId());
                // 注意：这里需要在Permission类中添加children属性和对应的setter方法
                // permission.setChildren(children);
            }

            return permissions;
        } catch (SQLException e) {
            throw new DatabaseException("查询权限树失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    // 辅助方法：将ResultSet映射到Permission对象
    private Permission mapResultSetToPermission(ResultSet rs) throws SQLException {
        Permission permission = new Permission();
        permission.setId(rs.getLong("id"));
        permission.setPermissionName(rs.getString("permission_name"));
        permission.setPermissionCode(rs.getString("permission_code"));
        permission.setDescription(rs.getString("description"));
        permission.setParentId(rs.getLong("parent_id"));
        permission.setResourcePath(rs.getString("resource_path"));
        permission.setSortOrder(rs.getInt("sort_order"));
        permission.setStatus(rs.getInt("status"));
        permission.setCreatedTime(rs.getObject("created_time", LocalDateTime.class));
        permission.setUpdatedTime(rs.getObject("updated_time", LocalDateTime.class));
        permission.setCreatedBy(rs.getLong("created_by"));
        permission.setUpdatedBy(rs.getLong("updated_by"));
        return permission;
    }

    // 辅助方法：构建查询条件
    private void buildQueryConditions(StringBuilder sql, Map<String, Object> params, List<Object> paramList) {
        if (params == null) {
            return;
        }

        if (params.containsKey("permissionName")) {
            sql.append(" AND permission_name LIKE ?");
            paramList.add("%" + params.get("permissionName") + "%");
        }

        if (params.containsKey("permissionCode")) {
            sql.append(" AND permission_code LIKE ?");
            paramList.add("%" + params.get("permissionCode") + "%");
        }

        if (params.containsKey("status")) {
            sql.append(" AND status = ?");
            paramList.add(params.get("status"));
        }

        if (params.containsKey("parentId")) {
            sql.append(" AND parent_id = ?");
            paramList.add(params.get("parentId"));
        }
    }

    // 辅助方法：关闭资源
    private void closeResources(PreparedStatement ps, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}